const mysql = require('mysql');

//  封装一个query方法
const dbconfig = {
    host: '127.0.0.1',
    user: 'root',
    password: '',
    database: 'cxblog',
    port: '3306',
    // 可以同时写多条sql语句
    multipleStatements: true
}

const pool = mysql.createPool(dbconfig)

function query(sql, params) {
    return new Promise(function(resolve, reject) {
        pool.getConnection(function(err, conn) {
            if (err) reject(err);
            conn.query(sql, params, function(err, rows) {
                if (err) reject(err);
                resolve(rows);
                conn.release();
            })
        })
    })
}


//娴后天轮播图
exports.getAllBanner = function() {
    let sql = 'select * from banner';
    return query(sql);
}

exports.addBanner = function(banner) {
    let sql = 'insert into banner set ?'
    return query(sql, banner)
}

exports.getOneBanner = function(id) {
    let sql = 'select * from banner where bannerId=?'
    return query(sql, id)
}

exports.editBanner = function(banner) {
    //不换图
    let sql = 'update banner set  linkUrl=?,status=? where bannerId=?';
    let params = [banner.linkUrl, banner.status, banner.bannerId];
    if (banner.imgName) {
        //换图
        sql = 'update banner set  imgName=?,linkUrl=?,status=? where bannerId=?';
        params = [banner.imgName, banner.linkUrl, banner.status, banner.bannerId];
    }
    return query(sql, params)
}

exports.deleteBanner = function(id) {
    let sql = 'delete from banner where bannerId=?';
    return query(sql, id)
}

exports.login = function(user) {
    let sql = 'select * from user where username=? and password=?'
    let params = [user.username, user.password]
    return query(sql, params)
}
exports.getNewArtical = function() {
    let sql = 'SELECT articalId,articalName,status,(select userName  from user where userId= artical.userId) as userName,(select className  from classify where classId= artical.classId) as className,updateTime  from artical order by updateTime desc limit 0,5;'
    return query(sql)
}


exports.login = function(user) {
    let sql = 'select * from user where username= ?and password=?';
    let params = [user.username, user.password]
    return query(sql, params);
}

// --------------------------------文章管理------------------------
exports.getAllArtical = function(currentPage) {
    currentPage = currentPage || 1;
    let start = (currentPage - 1) * 5;
    let sql = 'SELECT articalId,articalName,status,(select userName  from user where userId= artical.userId) as userName,(select className  from classify where classId= artical.classId) as className,createTime  from artical limit ?,5;select count(*) as total from artical; '
    return query(sql, start)
}
exports.addArticalPage = function() {
    let sql = 'select classId,className from classify';
    return query(sql)
}
exports.addArtical = function(artical) {
    let sql = 'insert into artical set ?';
    return query(sql, artical)
}
exports.deleteArtical = function(id) {
    let sql = 'delete from artical where articalId=?';
    return query(sql, id)
}
exports.editArticalPage = function(id) {
    let sql = 'select *from artical where articalId =?; select classId, className from classify';
    return query(sql, id)
}
exports.editArtical = function(artical) {
        let sql = 'update artical set classId=?,content=?, articalName=? where articalId=?';
        let params = [artical.classId, artical.content, artical.articalName, artical.articalId]
        return query(sql, params)
    }
    //----------------------------用户管理------------------------
    //页面显示所有数据
exports.getAllUser = function(currentPage) {
    currentPage = currentPage || 1;
    let start = (currentPage - 1) * 5;

    // let sql='select * from user'
    let sql = 'select * from user limit ?,5 ;select count(*) as total from user';

    return query(sql, start)
}

//新增
exports.addUser = function(user) {
    let sql = 'insert into user set ?'
    return query(sql, user)
}

//编辑
exports.getOneUser = function(id) {
    let sql = 'select * from user where userId=?'
    return query(sql, id)
}
exports.editUser = function(user) {
    let sql = 'update user set username=?,password=?,email=? ,status=? where userId=?'
    let params = [user.username, user.password, user.email, user.status, user.userId]
    return query(sql, params)
}

//删除
exports.deleteUser = function(id) {
        let sql = 'delete from user where userId=?'

        return query(sql, id)
    }
    //搜索
exports.searchUser = function(txt) {
    let sql = 'select * from user where username=?'
    return query(sql, txt)

}

// ----------------菜单管理----------------------
exports.getAllMenu = function() {
    let sql = 'select * from menu ';
    return query(sql);
}
exports.AddMenu = function(menu) {


    let sql = 'insert into menu set ?';
    return query(sql, menu);
}
exports.getOneMenu = function(menuId) {
    let sql = 'select * from menu where menuId = ?';
    return query(sql, menuId);
}
exports.editMenu = function(menu) {
    let params = [menu.menuName, menu.status, menu.linkUrl, menu.menuId]
    let sql = 'update menu set menuName=?,status=?,linkUrl =? where menuId = ? ';
    return query(sql, params);
}
exports.deleteMenu = function(menuId) {

    let sql = 'delete from menu where menuId =? ';
    return query(sql, menuId);
}

// --------------文章分类--------------------
exports.getAllClasses = function() {
    let sql = 'select * from classify ';
    return query(sql);
}
exports.AddClass = function(aClass) {


    let sql = 'insert into classify set ?';
    return query(sql, aClass);
}
exports.getOneClass = function(classId) {
    let sql = 'select * from classify where classId = ?';
    return query(sql, classId);
}
exports.editClass = function(aClass) {
    let params = [aClass.className, aClass.status, aClass.classId];
    let sql = 'update classify set className=?,status=?where classId = ? ';
    return query(sql, params);
}
exports.deleteClass = function(classId) {

    let sql = 'delete from classify where classId =? ';
    return query(sql, classId);



}